package com.example.mobileeducationtv;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	//Student Table Creation
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_NUMBER = "number";
	
	private static final String DATABASE_NAME = "mbedu";
	private static final int DATABASE_VERSION = 1;
	private static final String STUDENT_TABLE_NAME = "students";
	private static final String STUDENT_TABLE_CREATE = 
			"CREATE TABLE " + STUDENT_TABLE_NAME + " (" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_NAME +
			" TEXT , " + KEY_NUMBER + " TEXT);";
	
	//Messages Table Creation
	private static final String KEY_MID = "id";
	private static final String KEY_SENDER = "number";
	private static final String KEY_MESSAGE = "message";
	private static final String KEY_REPLY = "reply";
	
	private static final String MESSAGES_TABLE_NAME = "messages";
	private static final String MESSAGES_TABLE_CREATE = 
			"CREATE TABLE " + MESSAGES_TABLE_NAME + " (" + KEY_MID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_SENDER + 
			" TEXT , " + KEY_MESSAGE + " TEXT , " + KEY_REPLY + " TEXT);";
	
	
	DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	
	
	@Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(STUDENT_TABLE_CREATE);
        db.execSQL(MESSAGES_TABLE_CREATE);
    }

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + STUDENT_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + MESSAGES_TABLE_NAME);

		// Create tables again
		onCreate(db);
	}
	
	/*
	 * Student CRUD operations
	 */
	
	// get all students
	List<Student> getAllStudents() {
		SQLiteDatabase db = this.getWritableDatabase();
		List<Student> allStudents = new ArrayList<Student>();
		Cursor  cursor = db.rawQuery("select * from students;", null);
		
		if(cursor != null && cursor.moveToFirst()) {
			do {
				Student student = new Student();
				student.setName(cursor.getString(1));
				student.setNumber(cursor.getString(2));
				
				allStudents.add(student);
			} while (cursor.moveToNext());
		}
		return allStudents;
	}
	
	// add student
	void addStudent(Student student) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, student.getName());
		values.put(KEY_NUMBER, student.getNumber());

		db.insert(STUDENT_TABLE_NAME, null, values);
		db.close();
	}
	
	//get student Name
	Student getName(String number) {
		
		Student student = new Student(number, number);
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.query(STUDENT_TABLE_NAME, new String[] { KEY_NAME,
				KEY_NUMBER }, KEY_NUMBER + "=?",
				new String[] { String.valueOf(number) }, null, null, null, null);
		
		if (cursor != null) {
			cursor.moveToFirst();
			student.setName(cursor.getString(0));
		}
		
		//return student
		return student;
	}
	
	/*
	 * Messages CRUD Operations
	 */
	void addMessage(Messages message) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		
		values.put(KEY_SENDER, message.getSender());
		values.put(KEY_MESSAGE, message.getMessage());

		db.insert(MESSAGES_TABLE_NAME, null, values);
		Log.i(MESSAGES_TABLE_NAME, "sender = " + message.getSender());
		Log.i(MESSAGES_TABLE_NAME, "sender = " + message.getMessage());
		Log.i(MESSAGES_TABLE_NAME, "Message Added");
		db.close();
	}
	
	List<Messages> getAllMessages() {
		
		SQLiteDatabase db = this.getWritableDatabase();
		List<Messages> allMessages = new ArrayList<Messages>();

		Cursor cursor = db.rawQuery("select * from messages;", null);
		
		if(cursor != null && cursor.moveToFirst()) {
			do {
				Messages message = new Messages();
				message.setSender(cursor.getString(1));
				message.setMessage(cursor.getString(2));
				
				allMessages.add(message);
			} while (cursor.moveToNext());
		}
		return allMessages;
	}
	
	Messages getMessage(String number) {
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.query(MESSAGES_TABLE_NAME, new String[] { KEY_SENDER,
				KEY_MESSAGE }, KEY_SENDER + "=?",
				new String[] { String.valueOf(number) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();
		
		Messages message = new Messages(cursor.getString(0), cursor.getString(1));
		
		//return student
		return message;
	}
}
